﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtility;
using System.Data;
using System.Data.OracleClient;
namespace OracleDAL.Sec
{
    public class Operate
    {
       
        public Model.Sec.Operate GetModel(string OperateID)
        {
            string sql = "SELECT * FROM SEC_OPERATE_NEW WHERE BELONGSYSTEM='10' and OPERATEID='" + OperateID + "'";
            System.Data.DataSet ds = DBUtility.OracleHelper.Query(sql);
            System.Data.DataRow dr = null;
            if (ds.Tables[0].Rows.Count > 0)
                dr = ds.Tables[0].Rows[0];

            Model.Sec.Operate ope = new Model.Sec.Operate();

            ope.OperateID = OperateID;
            string tempParentOperateID = dr["TREEORDER"].ToString();
            if (!string.IsNullOrEmpty(tempParentOperateID) && tempParentOperateID.Length>2)
                ope.ParentOperateID = tempParentOperateID.Substring(0, tempParentOperateID.Length - 2);//去掉最后2位
            ope.OperateName = dr["OPERATENAME"].ToString();
            ope.OperateType = dr["OPERATETYPE"].ToString();
            ope.OperateURL = dr["OPERATEURL"].ToString().Split(new char[';']);
            ope.BelongSystem = dr["BELONGSYSTEM"].ToString();
            ope.TreeOrder = dr["TREEORDER"].ToString();
            ope.TreeCataLog = dr["TREECATALOG"].ToString();
            ope.RightIcon = dr["RIGHTICON"].ToString();
            ope.Common = dr["COMMON"].ToString();
            return ope;
        }
        /// <summary>
        /// 获取指定操作权限的子权限集合(集合中有两部分：用户有权限操作的菜单权限、用户没权限操作的按钮权限)
        /// </summary>
        /// <param name="ParentTreeOrder">父节点TreeOrder,为null时返回操作权限根节点</param>
        /// <param name="UserISN">用户ISN</param>
        public Model.Sec.OperateCollection GetChildOperates(string ParentTreeOrder, string UserISN)
        {
            System.Data.DataTable dt = this.GetByUser(ParentTreeOrder, UserISN);
            Model.Sec.OperateCollection oc = new Model.Sec.OperateCollection(dt.Rows.Count);
            for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
            {
                Model.Sec.Operate ope = new Model.Sec.Operate();

                ope.OperateID = dt.Rows[rowNum]["OPERATEID"].ToString();
                string tempParentOperateID = dt.Rows[rowNum]["TREEORDER"].ToString();
                if (!string.IsNullOrEmpty(tempParentOperateID) && tempParentOperateID.Length > 2)
                    ope.ParentOperateID = tempParentOperateID.Substring(0, tempParentOperateID.Length - 2);//去掉最后2位
                ope.OperateName = dt.Rows[rowNum]["OPERATENAME"].ToString();
                ope.OperateType = dt.Rows[rowNum]["OPERATETYPE"].ToString();
                ope.OperateURL = dt.Rows[rowNum]["OPERATEURL"].ToString().Split(new char[';']);
                ope.BelongSystem = dt.Rows[rowNum]["BELONGSYSTEM"].ToString();
                ope.TreeOrder = dt.Rows[rowNum]["TREEORDER"].ToString();
                ope.TreeCataLog = dt.Rows[rowNum]["TREECATALOG"].ToString();
                ope.RightIcon = dt.Rows[rowNum]["RIGHTICON"].ToString();
                ope.Common = dt.Rows[rowNum]["COMMON"].ToString();
                ope.PropertyInitialized = true;

                oc[rowNum] = ope;
            }
            return oc;
        }
        /// <summary>
        /// 根据父操作权限，查询用户的父操作的子操作权限
        /// </summary>
        /// <param name="ParentTreeOrder">父节点TreeOrder</param>
        /// <param name="UserISN">用户ISN</param>
        /// <returns>权限数据表</returns>
        private System.Data.DataTable GetByUser(string ParentTreeOrder, string UserISN)
        {
            string sql = "";
            if (ParentTreeOrder == null)
            {
                sql = "SELECT DISTINCT(so.OPERATEID),OPERATENAME,OPERATETYPE,so.BELONGSYSTEM,OPERATEURL,TREEORDER,TREELEVEL,TREECATALOG,RIGHTICON,COMMON ";
                sql += "FROM SEC_OPERATE_NEW so,SEC_USERGROUP_USERINFO SUU,SEC_USERGROUP_OPERATE SUO  WHERE ";
                sql += "length(treeorder)=2 ";//一级操作权限
                sql += "and SUU.USERISN='" + UserISN + "' ";//过滤用户组
                sql += "AND SUU.USERGROUPISN=SUO.USERGROUPISN ";//过滤用户组权限配置
                sql += "AND SUO.OPERATEID=so.OPERATEID ";//过滤权限
                sql += "AND SUO.ENABLED='1' ";//过滤权限
                sql += "AND so.BELONGSYSTEM='10' ";
                sql += "order by to_number(treeorder)";
            }
            else
            {
                sql = "SELECT DISTINCT(so.OPERATEID),OPERATENAME,OPERATETYPE,so.BELONGSYSTEM,OPERATEURL,TREEORDER,TREELEVEL,TREECATALOG,RIGHTICON,COMMON ";
                sql += "FROM SEC_OPERATE_NEW so,SEC_USERGROUP_USERINFO SUU,SEC_USERGROUP_OPERATE SUO  WHERE ";
                sql += "substr(treeorder,0,length(treeorder)-2) ='" + ParentTreeOrder + "' ";//上级操作权限
                sql += "and SUU.USERISN='" + UserISN + "' ";//过滤用户组
                sql += "AND SUU.USERGROUPISN=SUO.USERGROUPISN ";//过滤用户组权限配置
                sql += "AND SUO.OPERATEID=so.OPERATEID ";//过滤权限
                sql += "AND SUO.ENABLED='1' ";//过滤权限
                sql += "AND so.BELONGSYSTEM='10' ";
                sql += "order by to_number(treeorder)";
            }
            System.Data.DataTable dt = DBUtility.OracleHelper.Query(sql).Tables[0];
            return dt; 
        }
      /// <summary>
      /// 根据菜单的TREEORDER删除菜单项，删除相关用户组的权限
      /// </summary>
      /// <param name="TREEORDER"></param>
     
      /// <returns></returns>
        public bool delOperateMenu(string TREEORDER)
        {
            List<string> TranSql = new List<string>();
            //删除相关用户组的权限,删除顺序不能调
            string sql1 = "delete from sec_usergroup_operate where belongsystem='10' and operateid in( select o.operateid from sec_operate_new o where  o.belongsystem='10' and TREEORDER  like '" + TREEORDER + "%')";
            
            string sql = "delete from SEC_OPERATE_NEW  where  belongsystem='10' and TREEORDER  like '" + TREEORDER + "%'";
            TranSql.Add(sql1);
            TranSql.Add(sql);
           
            int rows = OracleHelper.ExecuteSqlTran(TranSql);
            if (rows > 0)
                return true;
            else
                return false;

        }
        
        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public bool ISExist(string strwhere)
        {
            string sql = "select count(*) from SEC_OPERATE_NEW where belongsystem='10' and " + strwhere;
             return OracleHelper.Exists(sql);

        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(Model.Sec.Operate model)
        {  
            StringBuilder sql = new StringBuilder();

            sql.Append("update SEC_OPERATE_NEW set OPERATENAME='" + model.OperateName + "' ");
            if (model.ParentOperateID != null)
                sql.Append(",PARENTOPERATEID='" + model.ParentOperateID + "' ");
            if (model.OperateType != null)
                sql.Append(",OPERATETYPE='" + model.OperateType + "' ");
            sql.Append(",BELONGSYSTEM='10' ");
            if (model.OperateURL != null)
                sql.Append(",OPERATEURL='" + model.OperateURL[0] + "' ");
            sql.Append(",MODEL='bs' ");
            if (model.RightIcon != null)
                sql.Append(",RIGHTICON='" + model.RightIcon + "' ");
            sql.Append(",TREEORDER='" + model.TreeOrder + "' ");
            sql.Append(",TREELEVEL='' ");
            if (model.TreeCataLog != null)
                sql.Append(", TREECATALOG='" + model.TreeCataLog + "' ");
            sql.Append("where   belongsystem='10' and operateid='" + model.OperateID + "'");
            

            int rows = OracleHelper.ExecuteSql(sql.ToString());
          
            if (rows > 0)
                return true;
            else
                return false;
           

        }
        /// <summary>
        /// 更新相似的treeorder
        /// </summary>
        /// <returns></returns>
        public bool updateRelate(string OldTreeOrder,string NewTreeOrder)
        {
            int len = OldTreeOrder.Length;
            //substr(treeorder,0,"+len+") 为只替换最前面的部分，防止它把后面相同的也替换了
            //substr('" + OldTreeOrder + "',0) 要替换的部分
            //NewTreeOrder 替换的部分
            //substr(treeorder,"+len+1+" 没有替换的部分
            //最后用||把替换侯的部分和没有替换的部分相加即为最终的
            string sql = "update SEC_OPERATE_NEW set treeorder=(replace(substr(treeorder,0,"+len+"),substr('" + OldTreeOrder + "',0),'" + NewTreeOrder + "'))||substr(treeorder,"+(len+1).ToString()+") where  belongsystem='10' and treeorder like '" + OldTreeOrder + "%'";
            int rows= OracleHelper.ExecuteSql(sql);
            if (rows > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public Model.Sec.Operate getModel(string strWhere)
        {
            Model.Sec.Operate model = new Model.Sec.Operate();
            string sql = "select * from SEC_OPERATE_NEW where  BELONGSYSTEM='10' ";
            if (strWhere.Trim() != "")
            {
                sql +=" and "+ strWhere;
            }
            OracleDataReader dr = OracleHelper.ExecuteReader(sql);
            while (dr.Read())
            {
                model.BelongSystem = dr["BELONGSYSTEM"].ToString();
                model.OperateID=dr["OPERATEID"].ToString();
                model.OperateName = dr["OPERATENAME"].ToString();
                model.OperateType = dr["OPERATETYPE"].ToString();
                model.OperateURL = dr["OPERATEURL"].ToString().Split(new char[';']);
                model.ParentOperateID = dr["PARENTOPERATEID"].ToString();
                model.RightIcon = dr["RIGHTICON"].ToString();
                model.TreeCataLog = dr["TREECATALOG"].ToString();
                model.TreeOrder = dr["TREEORDER"].ToString();
                model.Common = dr["COMMON"].ToString();
            }
            dr.Close();
            dr.Dispose();
            return model;
        }

        /// <summary>
        /// 因为返回的最大的treeOrder，参数treeOrder="0"时，返回第一层最大的treeOrder
        /// </summary>
        /// <param name="treeOrder"></param>
        /// <returns></returns>
        public string getMaxTreeOrder(string treeOrder)
        {
            string sql;
            if (treeOrder == "0")
                sql = "select max(treeorder) maxTreeOrder from sec_operate_new where belongsystem='10' and length(treeorder)=2";
            else
                sql = "select max(treeorder) maxTreeOrder from sec_operate_new where belongsystem='10' and treeorder like'" + treeOrder + "__'";
            OracleDataReader dr = OracleHelper.ExecuteReader(sql);
            string maxTreeOrder = "";
            while (dr.Read())
            {
                maxTreeOrder = dr["MAXTREEORDER"].ToString();
            }
            dr.Close();
            dr.Dispose();
            return maxTreeOrder;
        }
        /// <summary>
        /// 上下移动节点
        /// </summary>
        /// <param name="UptreeOrder">原来在上面的节点treeOrder</param>
        /// <param name="UpOperateID">原来在上面的节点OperateID</param>
        /// <param name="DownTreeOrder">原来在下面的节点treeOrder</param>
        /// <param name="DownOperateID">原来在下面的节点OperateID</param>
        /// <returns></returns>
        public bool UPDownMove(string UptreeOrder,string UpOperateID,string DownTreeOrder, string DownOperateID)
        {
            List<string> tranSql = new List<string>();
           
            //暂存down节点及其子节点
            int len = DownTreeOrder.Length;
            string TempTreeOrder=string.Empty;
            for(int i=0;i<len;i++)
                TempTreeOrder += "a";
            string TempChildSql = "update SEC_OPERATE_NEW set treeorder=(replace(substr(treeorder,0," + len + "),substr('" + DownTreeOrder + "',0),'" + TempTreeOrder + "'))||substr(treeorder," + (len + 1).ToString() + ") where  belongsystem='10' and treeorder like '" + DownTreeOrder + "%'";
           
            //更新UP节点及其子节点
            string ChildSql1 = "update SEC_OPERATE_NEW set treeorder=(replace(substr(treeorder,0," + len + "),substr('" + UptreeOrder + "',0),'" + DownTreeOrder + "'))||substr(treeorder," + (len + 1).ToString() + ") where  belongsystem='10' and treeorder like '" + UptreeOrder + "%'";
            //更新down节点及其子节点
            string ChildSql = "update SEC_OPERATE_NEW set treeorder=(replace(substr(treeorder,0," + len + "),substr('" + TempTreeOrder + "',0),'" + UptreeOrder + "'))||substr(treeorder," + (len + 1).ToString() + ") where  belongsystem='10' and treeorder like '" + TempTreeOrder + "%'";
            //tranSql.Add(sql);
            //tranSql.Add(sql1);
            tranSql.Add(TempChildSql);
            tranSql.Add(ChildSql1);
            tranSql.Add(ChildSql);
            int rows= OracleHelper.ExecuteSqlTran(tranSql);
            if (rows > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool add(Model.Sec.Operate model)
        {   
            List<string> tranSql=new List<string>();
            StringBuilder sql=new StringBuilder();
            sql.Append("insert into sec_operate_new(OPERATEID,TREECATALOG,PARENTOPERATEID,OPERATENAME,OPERATETYPE,BELONGSYSTEM,OPERATEURL,RIGHTICON,TREEORDER,COMMON) values ");
            sql.Append("('" + model.OperateID + "',");
            sql.Append("'" + model.TreeCataLog + "',");
            sql.Append("'" + model.ParentOperateID + "',");
            sql.Append("'" + model.OperateName + "',");
            sql.Append("'" + model.OperateType + "',");
            sql.Append("'10',");
            sql.Append("'" + model.OperateURL[0] + "',");
            sql.Append("'" + model.RightIcon + "',");
            sql.Append("'" + model.TreeOrder + "',");
            sql.Append("'" + model.Common + "')");
            //给所有用户组增加此菜单
            string sql1 = "insert into sec_usergroup_operate select distinct(usergroupisn) usergroupisn,'10' belongsystem,'"+model.OperateID+"' operateid, '0' enabled from sec_usergroup ";
            //给roots用户组赋予权限
            string sqlRoot = "update sec_usergroup_operate set ENABLED=1 where usergroupisn='roots' and operateid='" + model.OperateID + "'";
            tranSql.Add(sql.ToString());
            tranSql.Add(sql1);
            tranSql.Add(sqlRoot);
            int rows = OracleHelper.ExecuteSqlTran(tranSql);
            if (rows > 0)
                return true;
            else
                return false;

        }
        /// <summary>
        /// 根据OperateID查找下一级的Operate
        /// </summary>
        /// <param name="OperateID"></param>
        /// <returns></returns>
        public DataTable returnTable(string OperateID)
        {
            string sql = "select o.operateid,o.operatename,o.treeorder,o.operateurl,o.treecatalog,o.common from sec_operate_new o,sec_operate_new o1 where o.belongsystem=o1.belongsystem and o.belongsystem='10' and substr(o.treeorder,0,length(o.treeorder)-2)=o1.treeorder and o1.operateid='" + OperateID + "' order by to_number(o.treeorder)";
            return OracleHelper.Query(sql).Tables[0];

        }
        /// <summary>
        /// 修改节点时用
        /// </summary>
        /// <param name="TreeOrder"></param>
        /// <param name="OperateName"></param>
        /// <param name="TreeUrl"></param>
        /// <param name="operateID"></param>
        /// <returns></returns>
        public bool Update(string TreeOrder, string OperateName, string TreeUrl, string operateID)
        {
            string sql = "update sec_operate_new set operatename='" + OperateName + "',treeorder='" + TreeOrder + "',operateurl='" + TreeUrl + "' where operateid='" + operateID + "' and belongsystem='10'";
            int rows= OracleHelper.ExecuteSql(sql);
            if (rows > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 根据TreeOrder返回所有同级的TreeOrder
        /// </summary>
        /// <param name="treeOrder"></param>
        /// <returns></returns>
        public DataTable getAllOperateID(string treeOrder)
        {
            string sql;
            if (string.IsNullOrEmpty(treeOrder))
            {
                sql = "select distinct(operateid),treeorder,operatename,operatetype,operateurl,treecatalog,righticon,common from sec_operate_new where belongsystem='10' and length(treeorder)=2 order by to_number(treeorder)";
            }
            else
            {
               sql = "select distinct(operateid),treeorder,operatename,operatetype,operateurl,treecatalog,righticon,common from sec_operate_new where belongsystem='10' and substr(treeorder,0,length(treeorder)-2) ='" + treeOrder + "' order by to_number(treeorder)";
               //sql = "select distinct(operateid),treeorder,operatename,operatetype,operateurl,treecatalog,righticon from sec_operate_new where belongsystem='10' and treeorder like'" + treeOrder + "%' order by to_number(treeorder)";
                 //上级操作权限 
            }
            return OracleHelper.Query(sql).Tables[0];

        }
        /// <summary>
        /// 根据父OperateID查找下一级子OperateID，子OperateID以,号分开
        /// </summary>
        /// <param name="ParentOperateID"></param>
        /// <returns></returns>
        public string getChildOperateID(string ParentOperateID)
        {
            string sql = "select distinct(operateid) from sec_operate_new where substr(treeorder,0,length(treeorder)-2)=(select treeorder from sec_operate_new  where belongsystem='10' and operateid='" + ParentOperateID + "')";
            OracleDataReader dr = OracleHelper.ExecuteReader(sql);
            string ChildOperateID="";
            while (dr.Read())
                ChildOperateID += dr["OPERATEID"].ToString() + ",";
            return ChildOperateID;
        } 
        /// <summary>
        /// 获取所有的通用标识符
        /// </summary>
        /// <returns></returns>
        public DataTable getAllCommon()
        {
            string sql = "select distinct(common) from sec_operate_new where belongsystem='10' and common is not null";
            return OracleHelper.Query(sql).Tables[0];
        }
        /// <summary>
        /// 根据用户ID获取可以访问的网系ID，返回形式为,11,23,232,......,
        /// </summary>
        /// <param name="userGroup">用户所属用户组，形式为'test','wei'</param>
        /// <returns></returns>
        public string getNetID(string userGroup)
        {
            string sql = "select * from com_yybm sn where sn.MATCHCODE  in  (select operateid from sec_operate t where t.operateid in  (select su.operateid  from sec_usergroup_operate su  where su.usergroupisn in(" + userGroup + ")  and su.enabled=1))";
            string netID = ",";
            OracleDataReader dr=OracleHelper.ExecuteReader(sql);
            while (dr.Read())
            {
                netID += dr["YYBM"].ToString() + ",";
            }
            dr.Close();
            return netID;
        }
 	/// <summary>
        /// 根据用户组查找下一级的权限OperateID
        /// </summary>
        /// <param name="usergroupISNs">用户组，形式为'roots','87'</param>
        /// <param name="ParentOperateID">父页面的OPerateID</param>
        /// <returns></returns>
        public DataTable  GetOperateIDByUserGroupISN(string usergroupISNs, string ParentOperateID)
        {
            string sql = "select distinct(operateid),common from sec_operate_new where substr(treeorder,0,length(treeorder)-2)=(select treeorder from sec_operate_new  where  operateid='" + ParentOperateID + "') and operateid in (select distinct(operateid) from sec_usergroup_operate where USERGROUPISN in (" + usergroupISNs + ") and enabled='1' and belongsystem='10' )";

            return OracleHelper.Query(sql).Tables[0];

        }

    }
}
